package com.globalwave.datasync;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.globalwave.common.db.ConnectionProvider;
import com.globalwave.common.db.SqlUtils;
import com.globalwave.datasync.sqlgen.BaseSqlGenerator;



public class SyncConfigLoader {
	private final static Integer INTEGER_TRUE = new Integer(1) ;
    private final static int dbType = SqlUtils.DB_TYPE_ORACLE ;
    

    private Log log = LogFactory.getLog(this.getClass()) ;
    
	private static SyncConfigLoader instance = null ;
	
	public static SyncConfigLoader getInstance() {
		if (instance == null) {
			instance = new SyncConfigLoader() ;
		}
		return instance ;
	}
	/**
	 * key:dataSource#SyncName,value:SyncTable
	 */
	private static Map<String, SyncConfig> configs = null ;
	
	public SyncConfig getConfig(String dataSource, String syncCode) {
		String key = dataSource + "#" + syncCode ;
		return configs.get(key) ;
	}
	
	public void load(Connection conn) throws Exception {

		Map<String, SyncConfig> configsTemp = new HashMap<String, SyncConfig>() ;
		
	    PreparedStatement tablePs = null ;
	    PreparedStatement columnPs = null ;
		try {            
			String sql = "select * from SYNC_CONFIG ";
			tablePs = conn.prepareStatement(SqlUtils.getUnlockRead(dbType, sql));
			
			ResultSet resultSet = tablePs.executeQuery() ;
			
			while (resultSet.next()) {
				final Long id = resultSet.getLong("sync_config_id") ;
				final String code = resultSet.getString("sync_config_code") ;
				final String querySQL = resultSet.getString("select_sql") ;
				SyncConfig config = new SyncConfig(id, code) ;
				
				SyncTable source = config.getSource() ;
				SyncTable dest = config.getDestination() ;

				source.setDbCode(resultSet.getString("source_db_code"));
				dest.setDbCode(resultSet.getString("dest_db_code"));

				source.setDbType(resultSet.getInt("source_db_type"));
				dest.setDbType(resultSet.getInt("dest_db_type"));

				source.setTableName(resultSet.getString("source_table"));
				dest.setTableName(resultSet.getString("dest_table"));

				config.setSourceQuerySQL(querySQL) ;
				config.setDestinationInsertSQL(resultSet.getString("insert_sql")) ;
				config.setModeType(resultSet.getString("mode_type")) ;
				config.setFetchSize(resultSet.getInt("fetch_size")) ;
				config.setLog(resultSet.getInt("is_log")) ;
				
				sql = "select * from SYNC_COLUMNS where SYNC_CONFIG_ID=? order by line_number " ;
				columnPs = conn.prepareStatement(SqlUtils.getUnlockRead(dbType, sql));
				columnPs.setLong(1, id) ;
				ResultSet columnRs = columnPs.executeQuery() ;

				List<SyncColumn> srcColumns = new ArrayList<SyncColumn>() ;
				List<SyncColumn> destColumns = new ArrayList<SyncColumn>() ;
				List<SyncColumn> srcKeys = new ArrayList<SyncColumn>() ;
				List<SyncColumn> destKeys = new ArrayList<SyncColumn>() ;

				List<SyncColumn> srcFilters = new ArrayList<SyncColumn>() ;
				
				boolean isStarSelect = false ;
				while (columnRs.next()) {
					//SyncTable table = (columnRs.getInt("is_source") == C.INTEGER_TRUE)?source:dest ;
					final String columnCode = trimAndToLowerCase(columnRs.getString("sync_columns_code")) ;
					
					if ("*".equals(columnCode)) {
						isStarSelect = true ;
						if (columnRs.getInt("is_source") == INTEGER_TRUE) {
							srcColumns.addAll(getTableColumns(source)) ;
						} else {
							destColumns.addAll(getTableColumns(dest)) ;
						}
						continue ;
					}
					final SyncColumn column = new SyncColumn() ;
					column.setConvertor(columnRs.getString("convertor")) ;
					column.setSyncColumnsCode(columnCode) ;
					column.setDefaultValue(columnRs.getString("default_value")) ;
					
					if (columnRs.getInt("is_source") == INTEGER_TRUE) {
						if (columnRs.getInt("is_pk") == INTEGER_TRUE) {
							column.setRefColumnsCode(
									trimAndToLowerCase(columnRs.getString("ref_columns_code"))) ;
							srcKeys.add(column) ;
						} else if (columnRs.getInt("is_filter") == INTEGER_TRUE) {
							srcFilters.add(column) ;
						} else {
							srcColumns.add(column) ;
						}
					} else {
						if (columnRs.getInt("is_pk") == INTEGER_TRUE) {
							column.setRefColumnsCode(
									trimAndToLowerCase(columnRs.getString("ref_columns_code"))) ;
							destKeys.add(column) ;
						} else {
							destColumns.add(column) ;
						}
					}
				}
				
				/*
				if (!StringUtils.isEmpty(querySQL)) {
					srcColumns.addAll(getTableColumnsBySQL(querySQL, conn)) ;
				}*/
				
				if (isStarSelect) {
					removeNotUseColumns(srcColumns, destColumns, srcKeys) ;
					srcColumns = destColumns ;
				}

				source.setSyncColumns(srcColumns.toArray(new SyncColumn[]{})) ;
				source.setKeys(srcKeys.toArray(new SyncColumn[]{})) ;
				source.setFilter(srcFilters.toArray(new SyncColumn[]{})) ;

				dest.setSyncColumns(destColumns.toArray(new SyncColumn[]{})) ;
				dest.setKeys(destKeys.toArray(new SyncColumn[]{})) ;

				/*generateQuerySQL(config) ;
				generateInsertSQL(config) ;
				generateUpdateSQL(config) ;
				generateDeleteSQL(config) ;*/
				BaseSqlGenerator.getGenerator(config).build() ;
				
				if (resultSet.getInt("direction") == SyncConfig.DIRECTION_TWO_WAY) {
					SyncConfig revertConfig = (SyncConfig)config.clone() ;
					revertConfig.setSource(config.getDestination()) ;
					revertConfig.setDestination(config.getSource()) ;


					BaseSqlGenerator.getGenerator(revertConfig).build() ;
					/*
					generateQuerySQL(revertConfig) ;
					generateInsertSQL(revertConfig) ;
					generateUpdateSQL(revertConfig) ;
					generateDeleteSQL(revertConfig) ;*/
					
					configsTemp.put(source.getDbCode() + "#" + revertConfig.getSyncConfigCode(), revertConfig) ;
					
					if (log.isDebugEnabled()) {
						log(revertConfig) ;
					}
				}

				if (log.isDebugEnabled()) {
					log(config) ;
				}
				configsTemp.put(source.getDbCode() + "#" + config.getSyncConfigCode(), config) ;
			}
			
			configs = configsTemp ;
		} catch(final Exception e) {
			log.error("SyncConfig init error:", e) ;
		} finally {
		    if (tablePs != null) {
		    	tablePs.close() ;
		    }
		    if (columnPs != null) {
		    	columnPs.close() ;
		    }
		}
	}
	
	private String trimAndToLowerCase(String str) {
		if(str == null) {
			return null ;
		}
		
		return str.trim().toLowerCase() ;
	}
	
	private void removeNotUseColumns(List<SyncColumn> cols1, List<SyncColumn> cols2, List<SyncColumn> keys) {
		Set<String> cols1Keys =  new HashSet<String>() ;
		
		Set<String> primaryKeys =  new HashSet<String>() ;
		for(SyncColumn key:keys) {
			primaryKeys.add(key.getSyncColumnsCode()) ;
		}
		
		for (int i = cols1.size()-1 ; i >=0 ; i --) {
			SyncColumn current = cols1.get(i) ;
			
			// 不应今关键字
			if (primaryKeys.contains(current.getSyncColumnsCode())) {
				continue ;
			}
			
			cols1Keys.add(current.getSyncColumnsCode()) ;
		}

		Set<String> cols2Keys =  new HashSet<String>() ;
		for (int i = cols2.size()-1 ; i >=0 ; i --) {
			SyncColumn current = cols2.get(i) ;
			if (!cols1Keys.contains(current.getSyncColumnsCode())) {
				cols2.remove(i) ;
				continue ;
			}
			cols2Keys.add(current.getSyncColumnsCode()) ;
		}

		for (int i = cols1.size()-1 ; i >=0 ; i --) {
			SyncColumn current = cols1.get(i) ;
			if (!cols2Keys.contains(current.getSyncColumnsCode())) {
				cols1.remove(i) ;
				continue ;
			}
		}
	}
	

	protected Set<SyncColumn> getTableColumns(final SyncTable table) throws Exception {
        String sql = "select * from " + table.getTableName();
        return getTableColumnsBySQL(sql, table) ;
    }
    /*
    private static String getMetaSql(String sql) {
    	//return sql + " fetch first 1 rows only with ur " ;
    	return sql + " fetch first 1 rows only with ur " ;
    }*/
    /**
     * 配置表名，读取Rs6000/As400中的表结构，获得两库表的共同字段名称列表及keys数据类型。
     * 
     * @param sourceTable 400表名
     * @param tableName  6000表名
     * @param keysTypeMap key字段Map，用于返回key数据类型
     * @return 字段名称列表
     * @throws DbException
     */
	protected Set<SyncColumn> getTableColumnsBySQL(final String sql, final SyncTable table) throws Exception {
    	Set<SyncColumn> sourceColumns = null ;
        // 源数据表字段
        //PreparedStatement stm = connection.prepareStatement(getMetaSql(sql)) ;
    	PreparedStatement stm = null ;
    	ConnectionProvider connectionProvider = null ;
        try {
        	connectionProvider = new ConnectionProvider() ;
        	Connection conn = connectionProvider.getConnection(table.getDbCode()) ;
        	stm = conn.prepareStatement(SqlUtils.getFetchFirstXRowsSql(table.getDbType(), sql, 1)) ; 
	        ResultSetMetaData meta = stm.executeQuery().getMetaData() ;
	        
	        sourceColumns = new HashSet<SyncColumn>() ;
	        
	        int count = meta.getColumnCount() ;
	        for (int i = 0 ; i < count ; i ++) {
	        	final SyncColumn col = new SyncColumn() ;
	        	col.setSyncColumnsCode(meta.getColumnName(i+1).toLowerCase()) ;
	            sourceColumns.add(col) ;                    
	        }
        } finally {
        	if (stm != null) {
        		stm.close() ;
        	}
        	connectionProvider.closeAllConnection() ;
        }
          
        return sourceColumns ;
    }
	
	
    private void log(SyncConfig config) {
    	if (!log.isDebugEnabled()) {
    		return ;
    	}
    	log.debug("code:" + config.getSyncConfigCode()) ;
    	log.debug("source db code:" + config.getSource().getDbCode()) ;
    	log.debug("dest db code:" + config.getDestination().getDbCode()) ;
    	
    	log.debug("source table:" + config.getSource().getTableName()) ;
    	log.debug("dest table:" + config.getDestination().getTableName()) ;
    	
    	log.debug("source getSourceQuerySQL - " + config.getSourceQuerySQL()) ;
    	log.debug("source getSourceUpdateSQL - " + config.getSourceUpdateSQL()) ;
    	log.debug("source getDestinationQuerySQL - " + config.getDestinationQuerySQL()) ;
    	log.debug("source getDestinationInsertSQL - " + config.getDestinationInsertSQL()) ;
    	log.debug("source getDestinationUpdateSQL - " + config.getDestinationUpdateSQL()) ;
    	log.debug("source getDestinationDeleteSQL - " + config.getDestinationDeleteSQL()) ;
    }
}
